package com.cctcq.dao.impl;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.cctcq.dao.DaoException;
import com.cctcq.dao.DaoTemplate;
import com.cctcq.dao.RowMapper;
import com.cctcq.domain.Product;
import com.cctcq.util.jdbc.JdbcUtils;
import com.cctcq.util.jdbc.Page;
import com.cctcq.util.jdbc.PageUtil;
import com.cctcq.util.jdbc.Result;
import com.cctcq.util.jdbc.SearchQuery;

public class ProductDaoImpl extends DaoTemplate{
	DaoTemplate daoTemplate = new DaoTemplate();
	//log4j日志实例
	static Logger logger = Logger.getLogger (ProductDaoImpl.class) ;
	/*
	 * 根据地区查询产品(品读+细游)
	 */
	public Result getProductsPinByArea(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			StringBuilder sql = new StringBuilder();
			sql.append("SELECT tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL FROM ( SELECT b.productid, b.productname, b.departplace, b.MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.ExpectAccountNumber FROM ( SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId UNION SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId WHERE aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '2' ) b")   ;
			List<Object> list = new ArrayList<Object>();
			sql.append(" INNER JOIN (SELECT ProductId, AreaId FROM Destination UNION SELECT ProductId, MacroDestinationId AreaId FROM Product) c ON b.ProductId = c.ProductId") ;
			if (sq != null) {
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql.append(" INNER JOIN ( SELECT AreaId FROM Area WHERE AreaState = '0' AND Continent LIKE '%'+?+'%'")  ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql.append(" UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Country LIKE '%'+?+'%'")  ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql.append(" UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Province LIKE '%'+?+'%'") ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql.append(" UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND City LIKE '%'+?+'%'");
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql.append(" UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND District LIKE '%'+?+'%' ) tab ON c.areaid = tab.areaid");
					list.add(new String(sq.getContinent().trim()));
				}
			}
			sql.append(" INNER JOIN TourGroup d ON b.productid = d.productid RIGHT JOIN ( SELECT productid, (CreateDate) createdate FROM TourGroup ) tab1 ON b.productid = tab1.productid AND d.createdate = tab1.createdate INNER JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId WHERE b.SlowLookType = '品读' AND e.IsDefaultPrice = 1 ) tab2 LEFT JOIN ( SELECT h.productid, z.PictureURL FROM Route h LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE i.IsDefaultPicture = 1 ) tab5 ON tab2.productid = tab5.productid GROUP BY tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL");
			System.out.println(sql);
			System.out.println(sql.toString());
			StringBuilder sql2 = new StringBuilder();
			sql2.append("select count(*) from (" + sql + ") x");
			System.out.println(sql2);
			ps = conn.prepareStatement(sql2.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProduct(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	/*
	 * 根据地区查询产品(闲读+细游)
	 */
	public Result getProductsXianByArea(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL FROM ( SELECT b.productid, b.productname, b.departplace, b.MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.ExpectAccountNumber FROM ( SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId UNION SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId WHERE aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '2' ) b" ;
			List<Object> list = new ArrayList<Object>();
			sql += " INNER JOIN (SELECT ProductId, AreaId FROM Destination UNION SELECT ProductId, MacroDestinationId AreaId FROM Product) c ON b.ProductId = c.ProductId" ;
			if (sq != null) {
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " INNER JOIN ( SELECT AreaId FROM Area WHERE AreaState = '0' AND Continent LIKE '%'+?+'%'" ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Country LIKE '%'+?+'%'" ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Province LIKE '%'+?+'%'";
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND City LIKE '%'+?+'%'";
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND District LIKE '%'+?+'%' ) tab ON c.areaid = tab.areaid";
					list.add(new String(sq.getContinent().trim()));
				}
			}
			String sql2 = " INNER JOIN TourGroup d ON b.productid = d.productid RIGHT JOIN ( SELECT productid, (CreateDate) createdate FROM TourGroup ) tab1 ON b.productid = tab1.productid AND d.createdate = tab1.createdate INNER JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId WHERE b.SlowLookType = '品读' AND e.IsDefaultPrice = 1 ) tab2 LEFT JOIN ( SELECT h.productid, z.PictureURL FROM Route h LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE i.IsDefaultPicture = 1 ) tab5 ON tab2.productid = tab5.productid GROUP BY tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL";
			System.out.println(sql+sql2);
			String sq1 = "select count(*) from (" + sql+sql2 + ") x";
			System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql+sql2, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProduct(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	/*
	 * 根据地区查询产品(精读+细游)
	 */
	public Result getProductsJingByArea(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL FROM ( SELECT b.productid, b.productname, b.departplace, b.MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.ExpectAccountNumber FROM ( SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId UNION SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId WHERE aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '2' ) b" ;
			List<Object> list = new ArrayList<Object>();
			sql += " INNER JOIN (SELECT ProductId, AreaId FROM Destination UNION SELECT ProductId, MacroDestinationId AreaId FROM Product) c ON b.ProductId = c.ProductId" ;
			if (sq != null) {
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " INNER JOIN ( SELECT AreaId FROM Area WHERE AreaState = '0' AND Continent LIKE '%'+?+'%'" ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Country LIKE '%'+?+'%'" ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Province LIKE '%'+?+'%'";
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND City LIKE '%'+?+'%'";
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND District LIKE '%'+?+'%' ) tab ON c.areaid = tab.areaid";
					list.add(new String(sq.getContinent().trim()));
				}
			}
			String sql2 = " INNER JOIN TourGroup d ON b.productid = d.productid RIGHT JOIN ( SELECT productid, (CreateDate) createdate FROM TourGroup ) tab1 ON b.productid = tab1.productid AND d.createdate = tab1.createdate INNER JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId WHERE b.SlowLookType = '品读' AND e.IsDefaultPrice = 1 ) tab2 LEFT JOIN ( SELECT h.productid, z.PictureURL FROM Route h LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE i.IsDefaultPicture = 1 ) tab5 ON tab2.productid = tab5.productid GROUP BY tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL";
			//System.out.println(sql+sql2);
			String sq1 = "select count(*) from (" + sql+sql2 + ") x";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql+sql2, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProduct(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/*
	 * 根据地区查询产品(鉴读+细游)
	 */
	public Result getProductsJianByArea(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL FROM ( SELECT b.productid, b.productname, b.departplace, b.MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.ExpectAccountNumber FROM ( SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId UNION SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId WHERE aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '2' ) b" ;
			List<Object> list = new ArrayList<Object>();
			sql += " INNER JOIN (SELECT ProductId, AreaId FROM Destination UNION SELECT ProductId, MacroDestinationId AreaId FROM Product) c ON b.ProductId = c.ProductId" ;
			if (sq != null) {
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " INNER JOIN ( SELECT AreaId FROM Area WHERE AreaState = '0' AND Continent LIKE '%'+?+'%'" ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Country LIKE '%'+?+'%'" ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Province LIKE '%'+?+'%'";
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND City LIKE '%'+?+'%'";
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND District LIKE '%'+?+'%' ) tab ON c.areaid = tab.areaid";
					list.add(new String(sq.getContinent().trim()));
				}
			}
			String sql2 = " INNER JOIN TourGroup d ON b.productid = d.productid RIGHT JOIN ( SELECT productid, (CreateDate) createdate FROM TourGroup ) tab1 ON b.productid = tab1.productid AND d.createdate = tab1.createdate INNER JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId WHERE b.SlowLookType = '品读' AND e.IsDefaultPrice = 1 ) tab2 LEFT JOIN ( SELECT h.productid, z.PictureURL FROM Route h LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE i.IsDefaultPicture = 1 ) tab5 ON tab2.productid = tab5.productid GROUP BY tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL";
			//System.out.println(sql+sql2);
			String sq1 = "select count(*) from (" + sql+sql2 + ") x";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql+sql2, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProduct(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	/*
	 * 根据地区查询产品(体惠)
	 */
	public Result getProductsTihuiByArea(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL FROM ( SELECT b.productid, b.productname, b.departplace, b.MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.ExpectAccountNumber FROM ( SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId where aa.ServiceRank = '1' UNION SELECT aa.ProductId, aa.productname, aa.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Area cc ON aa.MacroDestinationId = cc.AreaId WHERE aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '1' ) b" ;
			List<Object> list = new ArrayList<Object>();
			sql += " INNER JOIN (SELECT ProductId, AreaId FROM Destination UNION SELECT ProductId, MacroDestinationId AreaId FROM Product) c ON b.ProductId = c.ProductId" ;
			if (sq != null) {
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " INNER JOIN ( SELECT AreaId FROM Area WHERE AreaState = '0' AND Continent LIKE '%'+?+'%'" ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Country LIKE '%'+?+'%'" ;
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND Province LIKE '%'+?+'%'";
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND City LIKE '%'+?+'%'";
					list.add(new String(sq.getContinent().trim()));
				}
				if (sq.getContinent() != null && !sq.getContinent().trim().equals("")) {
					sql += " UNION ALL SELECT AreaId FROM Area WHERE AreaState = '0' AND District LIKE '%'+?+'%' ) tab ON c.areaid = tab.areaid";
					list.add(new String(sq.getContinent().trim()));
				}
			}
			String sql2 = " INNER JOIN TourGroup d ON b.productid = d.productid RIGHT JOIN ( SELECT productid, (CreateDate) createdate FROM TourGroup ) tab1 ON b.productid = tab1.productid AND d.createdate = tab1.createdate INNER JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId WHERE e.IsDefaultPrice = 1 ) tab2 LEFT JOIN ( SELECT h.productid, z.PictureURL FROM Route h LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE i.IsDefaultPicture = 1 ) tab5 ON tab2.productid = tab5.productid GROUP BY tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab5.PictureURL";
			//System.out.println(sql+sql2);
			String sq1 = "select count(*) from (" + sql+sql2 + ") x";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql+sql2, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProduct(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	//根据产品 Id查询  相关产品 
//	public Result getProductsById(Page page, SearchQuery sq) throws UnsupportedEncodingException {
//		Connection conn = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		Product product = null;
//		try {
//			conn = JdbcUtils.getConnection();
//			String sql = "SELECT tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, j.PictureURL FROM ( SELECT b.productid, b.productname, b.departplace, b.MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.ExpectAccountNumber FROM ( SELECT aa.ProductId, aa.productname, aa.departplace, aa.MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid WHERE aa.ResourceCheckState = '审核' UNION SELECT aa.ProductId, aa.productname, aa.departplace, aa.MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid WHERE bb.TourGroupState = '收人中' AND aa.ResourceCheckState = '审核' ) b" ;
//			List<Object> list = new ArrayList<Object>();
//			sql += " INNER JOIN TourGroup d ON b.productid = d.productid RIGHT JOIN ( SELECT productid, (CreateDate) createdate FROM TourGroup ) tab1 ON b.productid = tab1.productid AND d.createdate = tab1.createdate INNER JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId WHERE e.tourgroupchargetype = '成人' AND e.IsDefaultPrice = 1 ) tab2 LEFT JOIN Route h ON tab2.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId WHERE i.IsDefaultPicture = 1" ;
//			if (sq != null) {
//				if (sq.getProductId() != null && !sq.getProductId().trim().equals("")) {
//					sql += " AND tab2.productid = ? " ;
//					list.add(new String(sq.getProductId().getBytes("iso8859-1"), "UTF-8").trim());
//				}
//			}
//			String sql2 = " GROUP BY tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, j.PictureURL";
//			System.out.println(sql+sql2);
//			String sq1 = "select count(*) from (" + sql+sql2 + ") x";
//			System.out.println(sq1);
//			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
//			for (int i = 0; i < list.toArray().length; i++) {
//				ps.setObject(i + 1, list.toArray()[i]);
//			}
//			rs = ps.executeQuery();
//			rs.next();
//			int totalRecords  = rs.getInt(1);
//			page = PageUtil.createPage(page, totalRecords);
//			ps = conn.prepareStatement(sql+sql2, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
//			for (int i = 0; i < list.toArray().length; i++) {
//				ps.setObject(i + 1, list.toArray()[i]);
//			}
//			
//			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
//			rs = ps.executeQuery();
//			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
//			List<Object> products = new ArrayList<Object>();
//			while (rs.next()) {
//				product = MappingProduct(rs);
//				products.add(product);
//			}
//			Result r = new Result();
//			r.setContent(products);
//			r.setPage(page);
//			return r;
//		} catch (SQLException e) {
//			logger.error(e);
//			throw new DaoException(e.getMessage(), e); 
//		} finally {
//			JdbcUtils.free(rs, ps, conn);
//		}
//	}
	
	
	//根据产品ID修改查询次数
	public int updateProduct(String productId, int productSearchCounter) {
		String sql = "update Product set productSearchCounter = ? where productId = ?";
		Object[] args = new Object[] { productSearchCounter + 1 , productId };
		int product = this.daoTemplate.update(sql, args);
		return product;
	}
	
	
	
	/*
	 * 根据地区查产品(体惠)
	 * **/
	
	//当季产品
	public Result getProductsIn(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT b.ProductId, b.ProductName FROM ProductSeason a LEFT JOIN Product b ON a.ProductId = b.ProductId WHERE a.StartDate <= getdate() AND a.EndDate >= getdate() and b.ServiceRank = '2'" ;
			List<Object> list = new ArrayList<Object>();
			//System.out.println(sql);
			String sq1 = "select count(*) from (" + sql + ") x";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductInOut(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	//非当季产品
	public Result getProductsOut(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT b.ProductId, b.ProductName FROM ProductSeason a LEFT JOIN Product b ON a.ProductId = b.ProductId WHERE a.StartDate > getdate() OR a.EndDate < getdate() and b.ServiceRank = '2'" ;
			List<Object> list = new ArrayList<Object>();
			//System.out.println(sql);
			String sq1 = "select count(*) from (" + sql + ") x";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductInOut(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	//结伴成团
	public Result getProductsTogether(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT a.ProductId, a.DepartDate, a.AmendDesc, b.ProductName, b.ServiceRank FROM ProductAmend a, Product b WHERE a.ProductId = b.ProductId AND a.Checked=1" ;
			List<Object> list = new ArrayList<Object>();
			//System.out.println(sql);
			String sq1 = "select count(*) from (" + sql + ") x";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductTogether(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	//根据鉴读大类型 查询鉴读特色(公务交流)
	public Result getJianFeature(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT DISTINCT(Feature) FROM Product WHERE AppreciationType='公务交流'" ;
			List<Object> list = new ArrayList<Object>();
			//System.out.println(sql);
			String sq1 = "select count(*) from (" + sql + ") x";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductFeature(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	//根据鉴读大类型 查询鉴读特色(会展商务)
	public Result getJianFeatureTwo(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT DISTINCT(Feature) FROM Product WHERE AppreciationType='会展商务'" ;
			List<Object> list = new ArrayList<Object>();
			//System.out.println(sql);
			String sq1 = "select count(*) from (" + sql + ") x";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductFeature(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	//根据 产品特色查询产品详情
	//根据产品 Id查询  相关产品 
	public Result getProductsByFeature(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab2.DepartDate, tab2.MarketCharge, tab2.TourGroupId, tab5.PictureURL, ISNULL(SUM(tab3.Incidentals), 0) Incidentals, SUM ( tab2.ExpectAccountNumber - isnull(tab4.num, 0)) Surplus FROM ( SELECT b.productid, b.productname, b.departplace, b.MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.ExpectAccountNumber FROM ( SELECT aa.ProductId, aa.productname, aa.departplace, aa.MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid" ;
			List<Object> list = new ArrayList<Object>();
			if (sq != null) {
				if (sq.getFeature() != null && !sq.getFeature().trim().equals("")) {
					sql += " WHERE aa.Feature = ? UNION SELECT aa.ProductId, aa.productname, aa.departplace, aa.MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa " ;
					list.add(new String(sq.getFeature().getBytes("iso8859-1"), "UTF-8").trim());
				}
				if(sq.getFeature() != null && !sq.getFeature().trim().equals(""))
				{
					sql +="INNER JOIN TourGroup bb ON aa.productid = bb.productid WHERE aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '2' AND aa.Feature = ?) b INNER JOIN TourGroup d ON b.productid = d.productid INNER JOIN ( SELECT productid, MAX (CreateDate) createdate FROM TourGroup GROUP BY productid ) tab1 ON b.productid = tab1.productid AND d.createdate = tab1.createdate INNER JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId WHERE e.IsDefaultPrice = 1) tab2 LEFT JOIN ( SELECT f.TourGroupId, SUM ( isnull( isnull(f.AirPrice, 0) + isnull(f.VisaPrice, 0) + isnull(f.OwnExpense, 0) + isnull(f.Other, 0) + isnull(f.Tip, 0), 0 )) Incidentals FROM TourGroupCharge f WHERE f.IsDefaultPrice = 1 GROUP BY f.TourGroupId ) tab3 ON tab2.TourGroupId = tab3.TourGroupId LEFT JOIN ( SELECT g.TourGroupId, isnull(SUM(g.num), 0) num FROM PredefinedGroupOrder g WHERE g.OrderState IN ('报名', '留位') GROUP BY g.TourGroupId ) tab4 ON tab2.TourGroupId = tab4.TourGroupId LEFT JOIN ( SELECT h.productid, z.PictureURL FROM Route h LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE i.IsDefaultPicture = 1 ) tab5 ON tab2.productid = tab5.productid ";
					list.add(new String(sq.getFeature().getBytes("iso8859-1"), "UTF-8").trim());
				}
			}
			String sql2 = "GROUP BY tab2.productid, tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab2.DepartDate, tab2.MarketCharge, tab2.TourGroupId, tab5.PictureURL";
			System.out.println(sql+sql2);
			String sq1 = "select count(*) from (" + sql+sql2 + ") x";
			System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql+sql2, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductByFeature(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/*
	 * 根据景区查询次数查询产品-品读
	 * 2012-09-12 更改sql(数据库字段改动)
	 */
	public Result getProductsByPopular(Page page, SearchQuery sq) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM ( SELECT aa.productid, aa.productname, z.PictureURL, '1' sequence, z.IsDefaultPicture FROM product aa LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND i.IsDefaultPicture = 1 INNER JOIN ( SELECT productid FROM ProductSeason a WHERE a.StartDate <= getdate() AND a.EndDate >= getdate()) tab1 ON aa.ProductId = tab1.ProductId AND aa.SlowLookType = '品读' AND ( aa.Popular = '推荐' OR aa.Popular = '热推' ) UNION SELECT b.productid, b.productname, b.PictureURL, '2' sequence, b.IsDefaultPicture FROM ( SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, z.PictureURL, z.IsDefaultPicture FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND i.IsDefaultPicture = 1 UNION SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, z.PictureURL, z.IsDefaultPicture FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND i.IsDefaultPicture = 1 WHERE bb.TourGroupState = '2' ) b INNER JOIN ( SELECT productid FROM ProductSeason a WHERE a.StartDate <= getdate() AND a.EndDate >= getdate()) tab1 ON b.ProductId = tab1.ProductId AND SlowLookType = '品读' UNION SELECT aa.productid, aa.productname, z.PictureURL, '3' sequence, z.IsDefaultPicture FROM product aa LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND i.IsDefaultPicture = 1 WHERE ( aa.Popular = '推荐' OR aa.Popular = '热推' ) AND aa.slowlooktype = '品读' ) tab2 WHERE tab2.IsDefaultPicture = 1 ORDER BY tab2.sequence ASC";
			//System.out.println(sql);
			String sq1 = sql.replace("SELECT * FROM", "SELECT COUNT(*) FROM").substring(0,3260);
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductByPopular(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	 /*
	  *带图片的闲读类线路展示(最新线路)
	  */
	public Result getProductsXianAndNew(Page page, SearchQuery sq) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM ( SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, z.PictureURL FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId WHERE aa.ServiceRank = '2' AND aa.SlowLookType = '闲读' AND i.IsDefaultPicture = 1 UNION SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, z.PictureURL FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND i.IsDefaultPicture = 1 WHERE aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '2' AND aa.SlowLookType = '闲读' AND i.IsDefaultPicture = 1 ) a ORDER BY a.createdate DESC";
			//System.out.println(sql);
			String sq1 = "select count(*) from (SELECT * FROM product a ) b";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductXianAndNew(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/*
	 * 闲读页面 闲读线路
	 * */
	public Result getProductsXian(Page page,SearchQuery sq)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM ( SELECT aa.productid, aa.productname, z.PictureURL, '1' sequence, aa.CreateDate, i.IsDefaultPicture FROM product aa LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND i.IsDefaultPicture = 1 INNER JOIN ( SELECT productid FROM ProductSeason a WHERE a.StartDate <= getdate() AND a.EndDate >= getdate()) tab1 ON aa.ProductId = tab1.ProductId AND SlowLookType = '闲读' AND ( aa.Popular = '推荐' OR aa.Popular = '热推' ) AND DATEDIFF( MONTH, aa.CreateDate, getdate()) <= 1 UNION SELECT aa.productid, aa.productname, z.PictureURL, '2' sequence, aa.CreateDate, i.IsDefaultPicture FROM product aa LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND i.IsDefaultPicture = 1 WHERE aa.SlowLookType = '闲读' AND DATEDIFF( MONTH, aa.CreateDate, getdate()) <= 1 UNION SELECT aa.productid, aa.productname, z.PictureURL, '3' sequence, aa.CreateDate, i.IsDefaultPicture FROM product aa LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND i.IsDefaultPicture = 1 INNER JOIN ( SELECT productid FROM ProductSeason a WHERE a.StartDate <= getdate() AND a.EndDate >= getdate()) tab1 ON aa.ProductId = tab1.ProductId AND aa.SlowLookType = '闲读' UNION SELECT aa.productid, aa.productname, z.PictureURL, '4' sequence, aa.CreateDate, i.IsDefaultPicture FROM product aa LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND i.IsDefaultPicture = 1 WHERE ( aa.Popular = '推荐' OR aa.Popular = '热推' ) AND aa.SlowLookType = '闲读' ) tab2 WHERE tab2.IsDefaultPicture = 1 ORDER BY tab2.sequence ASC, CreateDate DESC";
			//System.out.println(sql);
			String sq1 = sql.replace("SELECT * FROM", "SELECT COUNT(*) FROM").substring(0, 2159);
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductXian(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/**
	 * 查询精读类别的所有特色主题
	 * @param page
	 * @param sq
	 * @return
	 */
	public Result getProductsJingFeature(Page page, SearchQuery sq)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT DISTINCT (Feature) FROM Product WHERE Feature IS NOT NULL AND SlowLookType = '精读'";
			String sq1 = "select count(*) from (" + sql + ") a";
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductJingFeature(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/**
	 * 查询精读类别的所有地区(主题线路)
	 * @param page
	 * @param sq
	 * @return
	 */
	public Result getProductsJingAreaName(Page page, SearchQuery sq)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT DISTINCT ( CASE c.arealevel WHEN '1' THEN c.Continent WHEN '2' THEN c.Country WHEN '3' THEN c.Province WHEN '4' THEN c.City WHEN '5' THEN c.District END ) areaname FROM product a LEFT JOIN Destination b ON a.productid = b.productid LEFT JOIN Area c ON b.AreaId = c.AreaId WHERE a.SlowLookType = '精读' AND c.arealevel IS NOT NULL";
			String sq1 = "select count(*) from (" + sql + ") a";
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductJingFeature(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/**
	 * 查询精读类别的三张展示图片(主题线路)
	 * @param page
	 * @param sq
	 * @return
	 */
	public Result getProductsJingPic(Page page, SearchQuery sq)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT TOP 3 PictureURL, ProductId, ProductName FROM ( SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, z.PictureURL FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE i.IsDefaultPicture = 1 UNION SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, z.PictureURL FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid AND i.IsDefaultPicture = 1 LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE bb.TourGroupState = '收人中' AND aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '2' AND i.IsDefaultPicture = 1 ) a WHERE a.SlowLookType = '精读'";
			//System.out.println(sql);
			String sq1 = "select count(*) from (" + sql + ") a";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductsJingPic(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/**
	 * 查询精读类别的主题线路
	 * @param page
	 * @param sq
	 * @return
	 */
	public Result getProductsJing(Page page, SearchQuery sq)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select b.tourgroupname,a.marketcharge,c.ProductId from TourGroupCharge a inner join TourGroup b on a.tourgroupid = b.tourgroupid inner join product c on b.productid = c.productid where c.slowlooktype = '精读' and a.TourGroupChargeType = '标准'";
			String sq1 = "select count(*) from (" + sql + ") a";
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductJing(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	//鉴读  最新线路
	public Result getProductsJianNew(Page page, SearchQuery sq)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT b.productname, b.PictureURL, b.productid FROM ( SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, z.PictureURL FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE i.IsDefaultPicture = 1 UNION SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, z.PictureURL FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '2' AND i.IsDefaultPicture = 1 ) b WHERE b.SlowLookType = '鉴读' AND DATEDIFF( MONTH, b.CreateDate, getdate()) <= 9 ORDER BY b.CreateDate DESC";
			System.out.println(sql);
			String sq1 = sql.replace("SELECT b.productname, b.PictureURL, b.productid FROM", "select count(*) from").substring(0, 2165);
			System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductJianNew(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	//鉴读top4,各个类型
	public Result getProductsJianTop(Page page, SearchQuery sq)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT TOP 4 a.ProductId, a.ProductName, b.essence FROM product a LEFT JOIN area b ON a.MacroDestinationId = b.AreaId WHERE a.SlowLookType = '鉴读' ORDER BY a.CreateDate DESC";
			//System.out.println(sql);
			String sq1 = sql.replace("SELECT TOP 4 a.ProductId, a.ProductName, b.essence FROM", "select count(*) from").substring(0, 110);
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductJianTop(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	//根据产品ID查询产品
	public Result getProducts(Page page, SearchQuery sq)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from product ";
			List<Object> list = new ArrayList<Object>();
			if (sq != null) {
				if (sq.getProductId() != null && !sq.getProductId().trim().equals("")) {
					sql += "where ProductId = ?" ;
					list.add(sq.getProductId().trim());
				}
			}
			//System.out.println(sql);
			String sq1 = sql.replaceAll("[*]", "count(*)");
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductByFeature(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	/**
	 * 
	*    
	* 项目名称：slowlookj5     
	* 类描述：   
	* 创建人：cctcq_helin   
	* 创建时间：2012-8-28 下午02:04:30   
	* 修改人：cctcq_helin   
	* 修改时间：2012-8-28 下午02:04:30   
	* 修改备注：   查询最新推荐
	* @version    
	*
	 */
	public Result getProductsByMycenterNewProduct(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT * FROM ( SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, aa.MacroDestination, z.PictureURL FROM Product aa INNER JOIN ProductFee bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE aa.ResourceCheckState = 1 AND i.IsDefaultPicture = 1 UNION SELECT aa.ProductId, aa.ProductCode, aa.ProductName, aa.HowToPlay, aa.ProductConcept, aa.ProductModelURL, aa.ProductSlogan, aa.ProductPreface, aa.ProductSaleURL, aa.ProductDemoURL, aa.ProductGuideURL, aa.WebDocDemoURL, aa.WebAudioDemoURL, aa.WebVideoDemoURL, aa.Duration, aa.ResourceRequest, aa.Topography, aa.Climate, aa.Feature, aa.ServiceRank, aa.OverSea, aa.CreateDate, aa.Creator, aa.MarketCheckMan, aa.MarketCheckState, aa.ResourceCheckMan, aa.ResourceCheckState, aa.ResourceRemark, aa.PubState, aa.Popular, aa.Available, aa.SlowLookType, aa.AppreciationType, aa.departplace, aa.MacroDestination, z.PictureURL FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid LEFT JOIN Route h ON aa.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId LEFT JOIN ScenerySpotPic z ON j.ScenerySpotId = z.ScenerySpotId AND z.IsDefaultPicture = 1 AND j.IsDefaultPicture = 1 WHERE aa.Available = '1' AND aa.PubState = '发布' AND bb.DepartDate > getdate() AND aa.ServiceRank = '2' AND i.IsDefaultPicture = 1 ) a ORDER BY a.createdate DESC";
			//System.out.println(sql);
			String sq1 = "select count(*) from (SELECT * FROM product a ) b";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> products = new ArrayList<Object>();
			while (rs.next()) {
				product = MappingProductXianAndNew(rs);
				products.add(product);
			}
			Result r = new Result();
			r.setContent(products);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	public Product getProduct(String productId) {
		String sql = "select * from product where ProductId = ? ";
		Object[] args = new Object[] { productId };
		Object product = this.daoTemplate.find(sql, args, new RowMapper() {
			public Object mapRow(ResultSet rs) throws SQLException {
				Product product = new Product();
				product.setProductId(rs.getString(1));
				product.setProductCode(rs.getString(2));
				product.setProductName(rs.getString(3));
				product.setHowToPlay(rs.getString(4));
//				product.setPlayWhat(rs.getString(5));
//				product.setFollowMe(rs.getString(6));
//				product.setProductConcept(rs.getString(7));
//				product.setProductModelUrl(rs.getString(8));
//				product.setProductSlogan(rs.getString(9));
//				product.setProductPreface(rs.getString(10));
//				product.setProductSaleUrl(rs.getString(11));
//				product.setProductDemoUrl(rs.getString(12));
//				product.setProductGuideUrl(rs.getString(13));
//				product.setWebDocDemoUrl(rs.getString(14));
//				product.setWebAudioDemoUrl(rs.getString(15));
//				product.setWebVideoDemoUrl(rs.getString(16));
//				product.setDuration(rs.getInt(17));
//				product.setResourceRequest(rs.getString(18));
//				product.setTopography(rs.getString(19));
//				product.setClimate(rs.getString(20));
//				product.setFeature(rs.getString(21));
//				product.setServiceRank(rs.getString(22));
//				product.setOverSea(rs.getString(23));
//				product.setCreateDate(rs.getTimestamp(24));
//				product.setMarketCheckState(rs.getBoolean(27));
//				product.setResourceCheckState(rs.getBoolean(29));
//				product.setResourceRemark(rs.getString(30));
//				product.setPubState(rs.getString(31));
//				product.setMarketResourceUrl(rs.getString(32));
//				product.setPopular(rs.getString(33));
//				product.setIsGroup(rs.getString(34));
//				product.setIsGroupDesc(rs.getString(35));
//				product.setProductCoverUrl(rs.getString(36));
//				product.setAvailable(rs.getBoolean(37));
				return product;
			}
		});
		return (Product)product;
	}
	
	/*
	 * 查询签证资料
	 */
	public Result getVisaDescByTourGroupId(Page page, SearchQuery sq) throws UnsupportedEncodingException
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Product product = null;
	try {
		conn = JdbcUtils.getConnection();
		String sql = "select a.VisaDesc from Product a left join tourgroup b on a.productid = b.productid" ;
		List<Object> list = new ArrayList<Object>();
		if (sq != null) {
			if (sq.getTourGroupId() != null && !sq.getTourGroupId().trim().equals("")) {
				sql += " where b.tourgroupid =?" ;
				list.add(new String(sq.getTourGroupId().getBytes("iso8859-1"), "UTF-8").trim());
			}
		}
		//System.out.println(sql);
		String sq1 = sql.replace("a.VisaDesc", "count(*)");
		//System.out.println(sq1);
		ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		for (int i = 0; i < list.toArray().length; i++) {
			ps.setObject(i + 1, list.toArray()[i]);
		}
		rs = ps.executeQuery();
		rs.next();
		int totalRecords  = rs.getInt(1);
		page = PageUtil.createPage(page, totalRecords);
		ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		for (int i = 0; i < list.toArray().length; i++) {
			ps.setObject(i + 1, list.toArray()[i]);
		}
		
		ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
		rs = ps.executeQuery();
		rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
		List<Object> products = new ArrayList<Object>();
		while (rs.next()) {
			product = MappingProductVe(rs);
			products.add(product);
		}
		Result r = new Result();
		r.setContent(products);
		r.setPage(page);
		return r;
	} catch (SQLException e) {
		logger.error(e);
		throw new DaoException(e.getMessage(), e); 
	} finally {
		JdbcUtils.free(rs, ps, conn);
	}
}
	
	private Product MappingProduct(ResultSet rs) throws SQLException {
		Product product = new Product();
		product.setProductId(rs.getString(1));
		product.setProductName(rs.getString(2));//产品名字
		product.setDepartplace(rs.getString(3));//出发地
		product.setBookTip(rs.getString(4));//目的地
		product.setServiceRank(rs.getString(5));//服务类型
		product.setFeature(rs.getString(6));//产品类型
		product.setOverSea(rs.getString(7));//线路类型
		product.setProductSlogan(rs.getString(9));//产品封面
		return product;
	}
	private Product MappingProductVe(ResultSet rs) throws SQLException {
		Product product = new Product();
		product.setVisaDesc(rs.getString(1));
		return product;
	}
	
	//当季非当季
	private Product MappingProductInOut(ResultSet rs)throws SQLException
	{
		Product product=new Product();
		product.setProductId(rs.getString(1));
		product.setProductName(rs.getString(2));
		return product;
	}
	
	private Product MappingProductTogether(ResultSet rs)throws SQLException
	{
		Product product = new Product();
		product.setProductId(rs.getString(1));
		product.setClimate(rs.getString(2));//时间
		product.setProductConcept(rs.getString(3));//口号 介绍
		product.setProductName(rs.getString(4));
		product.setServiceRank(rs.getString(5));
		return product;
	}
	private Product MappingProductFeature(ResultSet rs)throws SQLException
	{
		Product product=new Product();
		product.setFeature(rs.getString(1));
		return product;
	}
	private Product MappingProductByFeature(ResultSet rs) throws SQLException {
			Product product = new Product();
			product.setProductId(rs.getString(1));
			product.setProductCode(rs.getString(2));
			product.setProductName(rs.getString(3));
			product.setHowToPlay(rs.getString(4));
			product.setProductConcept(rs.getString(5));
			product.setProductModelUrl(rs.getString(6));
			product.setProductSlogan(rs.getString(7));
			product.setProductPreface(rs.getString(8));
			product.setProductSaleUrl(rs.getString(9));
			product.setProductDemoUrl(rs.getString(10));
			product.setProductGuideUrl(rs.getString(11));
			product.setWebDocDemoUrl(rs.getString(12));
			product.setWebAudioDemoUrl(rs.getString(13));
			product.setWebVideoDemoUrl(rs.getString(14));
		return product;
	}
	
	private Product MappingProductByPopular(ResultSet rs) throws SQLException {
		Product product = new Product();
		product.setProductId(rs.getString(1));
		product.setProductName(rs.getString(2));
		product.setWebAudioDemoUrl(rs.getString(3));//图片路径
		return product;
	}
	private Product MappingProductXianAndNew(ResultSet rs) throws SQLException{
		Product product = new Product();
	    product.setProductId(rs.getString(1));
	    product.setProductName(rs.getString(3));
//	    product.setPlayWhat(rs.getString(5));
//	    product.setFeature(rs.getString(21));
	    product.setWebAudioDemoUrl(rs.getString(34));//图片路径
		return product;
	}
	private Product MappingProductXian(ResultSet rs) throws SQLException{
	    Product product = new Product();
	    product.setProductId(rs.getString(1));
	    product.setProductName(rs.getString(2));
	    product.setWebAudioDemoUrl(rs.getString(3));//图片路径
	    return product;
	}
	private Product MappingProductJingFeature(ResultSet rs) throws SQLException {
		Product product = new Product();
		product.setFeature(rs.getString(1));
		return product;
	}
	private Product MappingProductsJingPic(ResultSet rs) throws SQLException {
		Product product = new Product();
		product.setWebAudioDemoUrl(rs.getString(1));
		product.setProductId(rs.getString(2));
		product.setProductName(rs.getString(3));
		return product;
	}
	private Product MappingProductJing(ResultSet rs) throws SQLException {
		Product product = new Product();
		product.setProductId(rs.getString(3));
		product.setFeature(rs.getString(2));
		product.setProductName(rs.getString(1));
		return product;
	}
	private Product MappingProductJianNew(ResultSet rs) throws SQLException {
		Product product = new Product();
		product.setProductName(rs.getString(1));
		product.setWebAudioDemoUrl(rs.getString(2));//图片路径
		product.setProductId(rs.getString(3));
		return product;
	}
	
	private Product MappingProductJianTop(ResultSet rs) throws SQLException {
		Product product = new Product();
		product.setProductId(rs.getString(1));
		product.setProductName(rs.getString(2));
		product.setPopular(rs.getString(3));
		return product;
	}
}
